package simorg.domain.persistence;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import simorg.domain.TestCompoundResult;
import simorg.util.DatabaseConnection;

public class TestCompoundResultTable {

	public static void bootstrap(boolean drop) throws Exception {
		DatabaseConnection dc = new DatabaseConnection();
		try {
			Statement stmt = null;
		    String query = null;
		    if ( drop ) {
		    	query = "DROP TABLE IF EXISTS TEST_COMPOUND_RESULT";
		    	stmt = dc.getConnection().createStatement();
			    stmt.execute(query);
		    }
		    query = "CREATE TABLE IF NOT EXISTS TEST_COMPOUND_RESULT" +
		    	" (COMPOUND_ID INT, TEST_ID INT, PRIMARY_RESULT_ID INT, SECONDARY_RESULT_ID INT, " + 
		    	"PRIMARY KEY (COMPOUND_ID, TEST_ID))";
		    stmt = dc.getConnection().createStatement();
		    stmt.execute(query);
		}
		finally {
			dc.getConnection().close();
		}
	}
	public static void save(DatabaseConnection dc, TestCompoundResult testResult) {
		try {
			//System.err.println("Inserting " + testResult.getCompoundId() + " -> " + testResult.getTestId());
			String query = null;
			query = "INSERT INTO TEST_COMPOUND_RESULT (COMPOUND_ID, TEST_ID, PRIMARY_RESULT_ID, SECONDARY_RESULT_ID) " +
							"VALUES ( ?, ?, ?, ?)";
			PreparedStatement insert = dc.getConnection().prepareStatement(query);
			fillPreparedStatement(testResult, insert);
			insert.executeUpdate();
				
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	protected static ArrayList<TestCompoundResult> find(DatabaseConnection dc, QueryConstraints constraints) {
		ArrayList<TestCompoundResult> retval = new ArrayList<TestCompoundResult>();
		try {
			String query = "SELECT * FROM TEST " + constraints.getParameterString();
			PreparedStatement stmt = dc.getConnection().prepareStatement(query);
			constraints.fillPreparedStatement(stmt);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()){
				TestCompoundResult test = new TestCompoundResult();
				fillFromResultSet(test, rs);
		        retval.add(test);
		    }
		} catch (Exception e) {
			e.printStackTrace();
		}
		return retval;
	}
	
	public static TestCompoundResult findById(DatabaseConnection dc, int compoundId, int testId) {
		QueryConstraints constraints = new QueryConstraints();
		constraints.getConstraints().add(new QueryConstraint("COMPOUND_ID", compoundId));
		constraints.getConstraints().add(new QueryConstraint("TEST_ID", testId));
		ArrayList<TestCompoundResult> result = find(dc, constraints);		
		TestCompoundResult retval = result.size() > 0 ? result.get(0) : null;
		if ( retval == null ) {
			retval = new TestCompoundResult();
			retval.setCompoundId(compoundId);
			retval.setTestId(testId);
			retval.setPrimaryResultString(1);
			retval.setSecondaryResultString(1);
		}
		return retval;
	}
	
	public static void fillFromResultSet(TestCompoundResult a,ResultSet rs) throws Exception {
		a.setCompoundId(rs.getInt("COMPOUND_ID"));
		a.setTestId(rs.getInt("TEST_ID"));
		a.setPrimaryResultString(rs.getInt("PRIMARY_RESULT_ID"));
		a.setSecondaryResultString(rs.getInt("SECONDARY_RESULT_ID"));
		
	}

	public static void fillPreparedStatement(TestCompoundResult a, PreparedStatement stmt) throws Exception {
		stmt.setInt(1, a.getCompoundId());
		stmt.setInt(2, a.getTestId());
		stmt.setInt(3, a.getPrimaryResultString());
		stmt.setInt(4, a.getSecondaryResultString());
	}
	
	
	
}
